﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Xml.Linq;
using DataAccess.SQL;
using System.Collections.Generic;
using DataAccess.Oracle;
using System.Data.SqlClient;
using CommonLib;
using Framework;
using Entities;
using Entities.System;

namespace BLL.System
{
    /// <summary>
    /// Summary description for Security
    /// </summary>
    public class UserGroupBO : BusinessBase
    {
        public string getUserGroup(Dictionary<string, string> dicLike, Dictionary<string, string> dicEqual, int? pageSize, int? pageNum)
        {
            List<UserGroup> lst = new List<UserGroup>();
            var condition = getCondition(dicLike, dicEqual);

            string sql = string.Empty;

            sql = string.Format(@"SELECT ug.USER_GROUP_KEY, ug.USER_KEY,ug.GROUP_KEY,g.GROUP_NAME,g.GROUP_ID,g.GROUP_DESC
FROM  SBRM_USER_GROUP ug   
LEFT JOIN  sbrm_group g on ug.GROUP_KEY=g.GROUP_KEY 
where   g.ACTIVE = 'Y' {0}                                                   
ORDER BY ug.USER_GROUP_KEY  ", condition);

            try
            {
                lst = SqlHelper.ExecuteList<UserGroup>(sql, CommandType.Text);
                //总条数
                sql = string.Format(@"  select count(*)  as rowsCount   
FROM  SBRM_USER_GROUP ug   
LEFT JOIN  sbrm_group g on ug.GROUP_KEY=g.GROUP_KEY 
where   g.ACTIVE='Y' {0}    ", condition);
                object oCount = SqlHelper.ExecuteScalar(sql, CommandType.Text);
                int total = Convert.ToInt32(oCount);
                return (new Result<UserGroup> { rows = lst, total = total }).ToJson();

            }
            catch (Exception ex)
            {
                var error = new Status { Success = false, Message = ex.Message };
                return error.ToJson();

            }

        }

        public string addUserGroup(HttpContext context)
        {
            string strSql = string.Empty;

            UserGroupList obj = JsonHelper.FromJson<UserGroupList>(context.Request.Form["data"]);
            //imagine groupkey with no ,
            string[] keys = obj.GroupKeys;
            int len = keys.Length;

            int result = 1;

            using (SqlConnection connection = SqlHelper.Con)
            {
                connection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = connection;
                SqlTransaction tx = connection.BeginTransaction();
                cmd.Transaction = tx;
                try
                {

                    User user = context.Session["user"] as User;
                    for (int i = 0; i < len; i++)
                    {
                        //新增  
                        strSql = @"insert into  SBRM_USER_GROUP 
(USER_KEY, GROUP_KEY, UPDATE_USER_KEY, UPDATE_USER_ID, UPDATE_USER_NAME, UPDATE_DATE)
values
(@USER_KEY, @GROUP_KEY, @UPDATE_USER_KEY, @UPDATE_USER_ID, @UPDATE_USER_NAME, GETDATE() ) 
{0} ";
                        strSql = string.Format(strSql, Common.Common.ReturnIdentitySql);
                        //插入
                        cmd.CommandText = strSql;
                        var parms = new SqlParameter[] {
                            new SqlParameter{ ParameterName="@USER_KEY" , Value=obj.USER_KEY  },
                            new SqlParameter{ ParameterName="@GROUP_KEY" , Value= keys[i] },
                            new SqlParameter{ ParameterName="@UPDATE_USER_KEY" , Value= user.USER_KEY  },
                            new SqlParameter{ ParameterName="@UPDATE_USER_ID" , Value=user.USER_ID  },
                            new SqlParameter{ ParameterName="@UPDATE_USER_NAME" , Value=user.USER_NAME  }
                        };
                        cmd.Parameters.AddRange(parms);
                        object newID = cmd.ExecuteScalar();
                        //插入日志
                        result = insertLog(user, new UserGroup { GROUP_KEY = Convert.ToInt32(keys[i]), USER_GROUP_KEY = Convert.ToInt32(newID), USER_KEY = obj.USER_KEY }, "ADD", tx);
                    }
                    tx.Commit();
                }
                catch (Exception E)
                {
                    result = 0;
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
            if (result > 0) return (new Status { Success = true }).ToJson();
            else return (new Status { Success = false, Message = "执行异常!" }).ToJson();


        }

        private int insertLog(User user, UserGroup ug, string status, SqlTransaction trans)
        {
            string strSql = @"insert into  sbrm_user_group_log
(USER_GROUP_KEY, USER_KEY, GROUP_KEY, UPDATE_USER_KEY, UPDATE_USER_ID, UPDATE_USER_NAME, UPDATE_DATE, STATUS) 
values 
(@USER_GROUP_KEY, @USER_KEY, @GROUP_KEY, @UPDATE_USER_KEY, @UPDATE_USER_ID, @UPDATE_USER_NAME, GETDATE(), @STATUS)";
            if (trans != null)
                return SqlHelper.ExecuteNonQuery(trans, strSql, CommandType.Text, new SqlParameter[] {
                            new SqlParameter{ ParameterName="@USER_GROUP_KEY" , Value= ug.USER_GROUP_KEY    },
                            new SqlParameter{ ParameterName="@USER_KEY" , Value=ug.USER_KEY  },
                            new SqlParameter{ ParameterName="@GROUP_KEY" , Value= ug.GROUP_KEY },
                            new SqlParameter{ ParameterName="@UPDATE_USER_KEY" , Value= user.USER_KEY  },
                            new SqlParameter{ ParameterName="@UPDATE_USER_ID" , Value=user.USER_ID  },
                            new SqlParameter{ ParameterName="@UPDATE_USER_NAME" , Value=user.USER_NAME  },
                            new SqlParameter{ ParameterName="@STATUS" , Value=status  }});
            else
                return SqlHelper.ExecuteNonQuery(strSql, CommandType.Text, new SqlParameter[] {
                            new SqlParameter{ ParameterName="@USER_GROUP_KEY" , Value= ug.USER_GROUP_KEY    },
                            new SqlParameter{ ParameterName="@USER_KEY" , Value=ug.USER_KEY  },
                            new SqlParameter{ ParameterName="@GROUP_KEY" , Value= ug.GROUP_KEY },
                            new SqlParameter{ ParameterName="@UPDATE_USER_KEY" , Value= user.USER_KEY  },
                            new SqlParameter{ ParameterName="@UPDATE_USER_ID" , Value=user.USER_ID  },
                            new SqlParameter{ ParameterName="@UPDATE_USER_NAME" , Value=user.USER_NAME  },
                            new SqlParameter{ ParameterName="@STATUS" , Value=status  }});
        }

        public string delUserGroup(HttpContext context)
        {
            string sql = string.Empty;
            int result = 1;
            //删除
            List<UserGroup> lst = JsonHelper.FromJson<List<UserGroup>>(context.Request.Form["data"]);
            int len = lst.Count;
            string groupKeys = string.Empty;
            for (int i = 0; i < lst.Count; i++) groupKeys += string.Format("'{0}',", lst[i].USER_GROUP_KEY);
            groupKeys = groupKeys.Substring(0, groupKeys.Length - 1);
            sql = string.Format("delete from SBRM_USER_GROUP where  USER_GROUP_KEY in ({0})", groupKeys);

            using (SqlConnection connection = SqlHelper.Con)
            {
                connection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = connection;
                SqlTransaction tx = connection.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    cmd.CommandText = sql;
                    result = cmd.ExecuteNonQuery();
                    User user = context.Session["user"] as User;
                    for (int i = 0; i < len; i++)
                    {
                        //插入日志
                        result = insertLog(user, lst[i], "DELETE", tx);
                    }

                    tx.Commit();
                }
                catch (Exception E)
                {
                    result = 0;
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
            if (result > 0) return (new Status { Success = true }).ToJson();
            else return (new Status { Success = false, Message = "执行异常!" }).ToJson();
        }

    }
}

